#!/usr/bin/env python
#coding:utf-8
#copy from:http://bytes.com/topic/python/answers/637384-python-microsoft-access
"""
Basically just an API wrapped around Douglas Savitsky's code from http://www.ecp.cc/pyado.html
Recordset iterator taken from excel.py in Nicolas Lehuen's code from http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/440661
"""
AD_OPEN_KEYSET = 1
AD_LOCK_OPTIMISTIC = 3
import win32com.client

class AccessDb(object):
    """An Access connection"""
    def connect(self, data_source, user, pwd, mdw):
        """Returns a connection to the jet database
        NB use .Close() to close (NB title case unlike closing a file)"""
        connAccess = win32com.client.Dispatch(r'ADODB.Connection')
        # DSN syntax - http://support.microsoft.com/kb/193332 and http://www.codeproject.com/database/connectionstrings.asp?df=100&forumid=3917&exp=0&select=1598401
        #DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;USER ID=%s;PASSWORD=%s;Jet OLEDB:System Database=%s;" % (data_source, user, pwd, mdw)

        #for access 2007
        DSN = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;" % (data_source)
        #print DSN
        connAccess.Open(DSN)
        return connAccess

    def getRecordset(self, connAccess, SQL_statement):
        return Recordset(connAccess, SQL_statement)

class Recordset(object):
    """Recordset created from a query"""
    def __init__ (self, connAccess, SQL_statement):
        rs = win32com.client.Dispatch(r'ADODB.Recordset')
        rs.Open(SQL_statement, connAccess, AD_OPEN_KEYSET, AD_LOCK_OPTIMISTIC)
        self.rs = rs

    def getFields(self):
        """Get list of field names"""
        fields = [field.Name for field in self.rs.Fields]
        return fields

    def __iter__(self):
        """ Returns a paged iterator by default. See paged().
        """
        return self.paged()

    def paged(self,pagesize=128):
        """ Returns an iterator on the data contained in the sheet. Each row
            is returned as a dictionary with row headers as keys. pagesize is
            the size of the buffer of rows ; it is an implementation detail but
            could have an impact on the speed of the iterator. Use pagesize=-1
            to buffer the whole sheet in memory.
        """
        try:
            fields = self.getFields()
            #fields = [self.encoding(field.Name) for field in recordset.Fields]
            ok = True
            while ok:
                # Thanks to Rogier Steehouder for the transposing tip
                rows = zip(*self.rs.GetRows(pagesize))

                if self.rs.EOF:
                    # close the recordset as soon as possible
                    self.rs.Close()
                    self.rs = None
                    ok = False

                for row in rows:
                    yield dict(zip(fields, row))
        except:
            if self.rs is not None:
                self.rs.Close()
                del self.rs
            raise
